package com.rankeiot.core.excel;

import com.rankeiot.core.anno.Excel;
import com.rankeiot.core.dict.IFixedDict;
import com.rankeiot.core.translator.Translator;
import com.rankeiot.core.util.StringUtil;
import jxl.*;
import jxl.biff.formula.FormulaException;
import jxl.format.CellFormat;
import jxl.read.biff.BiffException;
import jxl.read.biff.WorkbookParser;
import jxl.write.*;
import lombok.SneakyThrows;

import java.io.*;
import java.lang.Boolean;
import java.lang.Number;
import java.lang.reflect.Field;
import java.net.URL;
import java.util.*;
import java.util.function.Function;

/**
 * 按Xls模板导出
 * 如需用Xlsx格式，用XlsxTemplate
 */
public class XlsTemplateExporter {
    private String templatePath;
    private InputStream template;
    private int startRow;
    private int sheetIndex = 0;
    private Map<String, Object> cellDataMap = new HashMap<>();
    private List<Object> data;
    private final Map<String, Translator> translators = new HashMap<>();
    private Class rowTemplate;
    private Map<Integer, Function<Object, Object>> cellValueGetter = new HashMap<>();
    private Map<String,String> fieldMap=null;
    private boolean isMapList = false;

    public XlsTemplateExporter(String templatePath, int startRow) {
        this.templatePath = templatePath;
        this.startRow = startRow;
    }
    public XlsTemplateExporter(InputStream template, int startRow) {
        this.template = template;
        this.startRow = startRow;
    }
    public XlsTemplateExporter addTranslator(String field, Translator translator) {
        translators.put(field, translator);
        return this;
    }

    public XlsTemplateExporter setListData(List data) {
        this.data = data;
        if (data.size() > 0) {
            if (rowTemplate == null) {
                Class firstRowClass = data.get(0).getClass();
                if (Map.class.isAssignableFrom(firstRowClass)) {
                    isMapList = true;
                }else{
                    this.rowTemplate = firstRowClass;
                }
            }
        }
        return this;
    }
    public  XlsTemplateExporter setFieldMap(Map<String,String> fieldMap){
        this.fieldMap=fieldMap;
        return this;
    }
    public XlsTemplateExporter setRowTemplate(Class rowTemplate) {
        this.rowTemplate = rowTemplate;
        return this;
    }

    /**
     * 设置单元格的数据,可以为公式，如"=SUM(A$row:B$row)"
     *
     * @param address 单元格地址，如A1
     * @param value
     */
    public XlsTemplateExporter setCellData(String address, Object value) {
        cellDataMap.put(address, value);
        return this;
    }
    public XlsTemplateExporter setCellData(Map<String,Object> values) {
        cellDataMap.putAll(values);
        return this;
    }
    private InputStream getInputStream(String resource){
        if(resource==null){
            return null;
        }
        if(resource.startsWith("/")){
            return  getClass().getResourceAsStream(resource);
        }else if(resource.startsWith("http://")||resource.startsWith("https://")){
            try {
                return new URL(resource).openStream();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }else{
            try {
                return new FileInputStream(resource);
            } catch (FileNotFoundException e) {
                throw new RuntimeException(e);
            }
        }
    }
    public void export(OutputStream out) throws Exception {
        InputStream in = template==null ? getInputStream(templatePath):template;

        WorkbookParser w= (WorkbookParser) Workbook.getWorkbook(in);
        WorkbookSettings settings = w.getSettings();
        settings.setWriteAccess(null);
       // settings.setNamesDisabled(true);
        WritableWorkbook nw = Workbook.createWorkbook(out,w ,settings);
        nw.setProtected(false);

        WritableSheet ws = nw.getSheet(sheetIndex);

        //写入表格数据
        for (Map.Entry<String, Object> entry : cellDataMap.entrySet()) {
            Object value = entry.getValue();
            String address = entry.getKey();
            writeCell(address, value, ws);
        }
        if (data != null && !data.isEmpty()) {
            //写入数据行
            //获取模板行
            if(rowTemplate == null){
                if(isMapList){
                    if(fieldMap==null){
                        throw new RuntimeException("请设置行字段映射");
                    }
                    initRowFromFieldMap();
                }else{
                   throw new RuntimeException("请设置行模板类");
                }
            }else{
                initRowTemplate(rowTemplate);
                initRowTemplate(rowTemplate.getSuperclass());
            }


            int cols = ws.getColumns();

            CellWriter[] writers = new CellWriter[cols];
            for (int i = 0; i < cols; i++) {
                writers[i] = new CellWriter(ws.getWritableCell(i, startRow));
            }
            CellView cellView = ws.getRowView(startRow);
            int row = startRow;
            for (Object data : this.data) {
               // ws.insertRow(row);
                for (int i = 0; i < cols; i++) {
                    writers[i].write(data, row, ws);
                }
                ws.setRowView(row, cellView);
                row++;
            }
        }
        nw.write();
        nw.close();
    }

    private static int excelColumnToNumber(String column) {
        char[] chars = column.toUpperCase().toCharArray();
        int result = 0;
        for (char c : chars) {
            result = result * 26 + (c - 'A' + 1);
        }
        return result - 1;
    }
    private void initRowFromFieldMap(){

        for(Map.Entry<String,String> entry:fieldMap.entrySet()){
            String columnName=entry.getKey();
            String fieldName=entry.getValue();
            char firstCode=columnName.charAt(0);
            int colNum=0;
            if(firstCode>=48&&firstCode<=57){
                colNum = Integer.valueOf(columnName);
            }else{
                colNum = excelColumnToNumber(columnName);
            }
            if(isMapList){
                cellValueGetter.put(colNum, new MapGetter(fieldName));
            }else{
                try {
                    Field field=rowTemplate.getDeclaredField(fieldName);
                    cellValueGetter.put(colNum, new FieldValueGetter(field));
                } catch (NoSuchFieldException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
    private void initRowTemplate(Class exportDef) {
        if (exportDef == Object.class) {
            return;
        }
        for (Field field : exportDef.getDeclaredFields()) {
            Excel export = field.getAnnotation(Excel.class);
            if (export == null) {
                continue;
            }
            if (!export.out()) {
                continue;
            }
            //获取列号
            int colNum = -1;
            if (StringUtil.isNotEmpty(export.columnName())) {
                colNum = excelColumnToNumber(export.columnName());
            } else {
                colNum = export.column();
            }
            if (cellValueGetter.containsKey(colNum)) {
                continue;
            }
            if (isMapList) {
                cellValueGetter.put(colNum, new MapGetter(field.getName()));
            } else {
                cellValueGetter.put(colNum, new FieldValueGetter(field));
            }
        }
    }

    private void writeCell(String address, Object value, WritableSheet ws) throws WriteException {
        if (value == null) {
            return;
        }
        WritableCell wc = ws.getWritableCell(address);
        WritableCell targetCell = wc;
        if (value instanceof CharSequence) {
            String strValue = value.toString();
            if (StringUtil.isEmpty(strValue)) {
            } else if (strValue.startsWith("=")) {
                targetCell = new Formula(wc.getColumn(), wc.getRow(), strValue.substring(1));
            } else {
                targetCell = new Label(wc.getColumn(), wc.getRow(), strValue);
            }
        } else if (value instanceof Number) {
            targetCell = new jxl.write.Number(wc.getColumn(), wc.getRow(), Double.valueOf(value.toString()));
        } else if (value instanceof Date) {
            targetCell = new DateTime(wc.getColumn(), wc.getRow(), (Date) value);
        } else if (value instanceof Boolean) {
            targetCell = new jxl.write.Boolean(wc.getColumn(), wc.getRow(), (Boolean) value);
        }
        CellFormat originFormat = wc.getCellFormat();
        if (originFormat != null) {
            targetCell.setCellFormat(originFormat);
        }
        WritableCellFeatures wf = wc.getWritableCellFeatures();
        if (wf != null) {
            targetCell.setCellFeatures(wf);
        }
        ws.addCell(targetCell);
    }

    private class MapGetter implements Function {
        private String key;
        private Translator translator;

        public MapGetter(String key) {
            this.key = key;
            translator = translators.get(key);
        }

        @Override
        public Object apply(Object o) {
            if (o == null) {
                return null;
            }
            Map m = (Map) o;
            Object value = m.get(key);
            if (translator != null) {
                return translator.translate(o, key, value);
            }
            return value;
        }
    }

    private class FieldValueGetter implements Function {
        private String key;
        private Field field;
        private Translator translator;

        public FieldValueGetter(Field field) {
            this.field = field;
            this.key = field.getName();
            field.setAccessible(true);
            translator = translators.get(key);
        }

        @SneakyThrows
        @Override
        public Object apply(Object o) {
            if (o == null) {
                return null;
            }
            Object value = field.get(o);
            if (translator != null) {
                return translator.translate(o, key, value);
            }
            return value;
        }
    }

    private class CellWriter {
        private WritableCell origin;
        private Function getter;
        private String formula;

        public CellWriter(WritableCell origin) throws FormulaException {
            this.origin = origin;
            getter = cellValueGetter.get(origin.getColumn());
            CellType ct = origin.getType();
            //单独处理公式
            if (origin instanceof FormulaCell) {
                FormulaCell nc = (FormulaCell) origin;
                formula = nc.getFormula();
            }
        }

        private WritableCell copyStyleTo(WritableCell wc) {
            CellFormat cf = origin.getCellFormat();
            if (cf != null) {
                wc.setCellFormat(cf);
            }
            WritableCellFeatures wf = origin.getWritableCellFeatures();
            if (wf != null) {
                wc.setCellFeatures(wf);
            }
            return wc;
        }

        public void write(Object value, int row, WritableSheet ws) throws WriteException {
            if (value == null || getter == null) {
                copyToRow(row, ws);
            } else {
                writeToRow(row, getter.apply(value), ws);
            }
        }

        private void copyToRow(int row, WritableSheet ws) throws WriteException {
            if (formula != null) {
                String newFormula = formula.replaceAll("(\\w+)" + (startRow + 1), "$1" + (row + 1));
                Formula f = new Formula(origin.getColumn(), row, newFormula);
                copyStyleTo(f);
                ws.addCell(f);
            } else {
                WritableCell wc = origin.copyTo(origin.getColumn(), row);
                ws.addCell(wc);
            }
        }

        private void writeToRow(int row, Object value, WritableSheet ws) throws WriteException {
            if (value == null) {
                copyToRow(row, ws);
                return;
            }
            WritableCell targetCell;
            if (value instanceof String) {
                //公式
                String strValue = (String) value;
                if (((String) value).startsWith("=")) {
                    String formula = strValue.substring(1);
                    formula = formula.replaceAll("\\$row", row + "");
                    formula = formula.replaceAll("\\$index", (row - startRow) + "");
                    targetCell = new Formula(origin.getColumn(), row, formula);
                } else {
                    targetCell = new Label(origin.getColumn(), row, strValue);
                }
            } else if (value instanceof Number) {
                Number number = (Number) value;
                Double d = number.doubleValue();
                targetCell = new jxl.write.Number(origin.getColumn(), row, d);
            } else if (value instanceof Boolean) {
                Boolean b = (Boolean) value;
                targetCell = new jxl.write.Boolean(origin.getColumn(), row, b);
            } else if (value instanceof Date) {
                Date date = (Date) value;
                targetCell = new DateTime(origin.getColumn(), row, date);

            } else if (value instanceof IFixedDict) {
                targetCell = new Label(origin.getColumn(), row, ((IFixedDict) value).getValue());
            } else if (value instanceof Enum<?>) {
                targetCell = new Label(origin.getColumn(), row, ((Enum) value).name());

            } else {
                targetCell = new Label(origin.getColumn(), row, value.toString());
            }
            copyStyleTo(targetCell);
            ws.addCell(targetCell);
        }
    }

}
